﻿using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace KPIS.GERP.ESS.DAL
{
    public class D_BgmProjectOperation
    {
        ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public string Insert(M_BGM_ProjectOperation info)
        {
            string strSQL;
            string strSeq = "";
            string strNewCode = "";
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                //dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_code", info.bgm_proj_oper_code == null ? DBNull.Value : (object)info.bgm_proj_oper_code);
                dbManager.AddParameters(2, "@bgm_proj_oper_name", info.bgm_proj_oper_name == null ? DBNull.Value : (object)info.bgm_proj_oper_name);
                dbManager.AddParameters(3, "@bgm_proj_oper_descr", info.bgm_proj_oper_descr == null ? DBNull.Value : (object)info.bgm_proj_oper_descr);
                dbManager.AddParameters(4, "@bgm_proj_oper_date", info.bgm_proj_oper_date == null ? DBNull.Value : (object)info.bgm_proj_oper_date);
                dbManager.AddParameters(5, "@start_date", info.start_date == null ? DBNull.Value : (object)info.start_date);
                dbManager.AddParameters(6, "@end_date", info.end_date == null ? DBNull.Value : (object)info.end_date);
                dbManager.AddParameters(7, "@bgm_year", info.bgm_year == null ? DBNull.Value : (object)info.bgm_year);
                dbManager.AddParameters(8, "@bgm_proj_seq", info.bgm_proj_seq == null ? DBNull.Value : (object)info.bgm_proj_seq);
                dbManager.AddParameters(9, "@emp_seq", info.emp_seq == null ? DBNull.Value : (object)info.emp_seq);
                dbManager.AddParameters(10, "@job_site_seq", info.job_site_seq == null ? DBNull.Value : (object)info.job_site_seq);
                dbManager.AddParameters(11, "@is_use", info.is_use == null ? DBNull.Value : (object)info.is_use);
                dbManager.AddParameters(12, "@attend_count", info.attend_count == null ? DBNull.Value : (object)info.attend_count);

                dbManager.AddParameters(13, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(14, "@created_by", info.created_by);
                dbManager.AddParameters(15, "@created_when", info.created_when);

                strSQL = "select op.bgm_proj_seq, coalesce(max(op.bgm_proj_oper_code), '0') as max_code, pj.bgm_proj_code"
                    + " from bgm_project_operation op"
                    + " left join bgm_project pj on op.bgm_proj_seq = pj.bgm_proj_seq"
                    + " where op.bgm_proj_seq = @bgm_proj_seq"
                    + " and op.record_status not like 'D'"
                    + " group by op.bgm_proj_seq, pj.bgm_proj_code";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0 && !string.IsNullOrEmpty(dtb.Rows[0]["max_code"].ToString()))
                {
                    strNewCode = dtb.Rows[0]["max_code"].ToString();
                    string[] strArray = strNewCode.Split('-');
                    string strRunning = "00";
                    int i;

                    if (strArray.Count() > 0)
                    {
                        strRunning = strArray[strArray.Count() - 1];
                    }

                    strRunning = (int.TryParse(strRunning, out i) ? Convert.ToInt32(strRunning) + 1 : 1).ToString("00");
                    strNewCode = dtb.Rows[0]["bgm_proj_code"].ToString() + "-" + strRunning;
                }
                else
                {
                    strSQL = "select bgm_proj_code"
                        + " from bgm_project"
                        + " where bgm_proj_seq = @bgm_proj_seq";
                    DataTable dtb2 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                    if (dtb2 != null && dtb2.Rows.Count > 0)
                    {
                        strNewCode = dtb2.Rows[0][0].ToString() + "-01";
                    }
                }

                strSQL = "INSERT INTO bgm_project_operation"
                    + "(bgm_proj_oper_code, bgm_proj_oper_name, bgm_proj_oper_descr, bgm_proj_oper_date"
                    + ", start_date, end_date, bgm_year, bgm_proj_seq, emp_seq, job_site_seq, is_use, attend_count"

                    + ", record_status, created_by, created_when)"
                    + " VALUES ('" + strNewCode + "'"
                        + ", @bgm_proj_oper_name"
                        + ", @bgm_proj_oper_descr"
                        + ", @bgm_proj_oper_date"
                        + ", @start_date"
                        + ", @end_date"
                        + ", @bgm_year"
                        + ", @bgm_proj_seq"
                        + ", @emp_seq"
                        + ", @job_site_seq"
                        + ", @is_use"
                        + ", @attend_count"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ");"
                        + " SELECT scope_identity();";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = 
                //if (ds.Tables[0].Rows.Count > 0)
                //{
                //    strSeq = ds.Tables[0].Rows[0][0].ToString();
                //}
                //dbManager.CommitTransaction();

                return strSeq;
            }
            catch (Exception ex)
            {
                //dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetProjectRemainAmount(int? bgm_proj_seq, int? bgm_source_seq)
        {
            DataTable dtb = null;
            string strSQL = "";
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                //dbManager.BeginTransaction();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                strSQL = @"
select pjexp.bgm_source_seq, sum(coalesce(cal_amount, 0)) as amount
from bgm_project_report rpt
left join bgm_project_expense pjexp on rpt.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq
where rpt.record_status <> 'D'"
+ (bgm_proj_seq.HasValue ? " and pjexp.bgm_proj_seq = @bgm_proj_seq" : "")
+ (bgm_source_seq.HasValue ? " and pjexp.bgm_source_seq = @bgm_source_seq" : "")
+ @" group by pjexp.bgm_source_seq
";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    return cn.ConvertNullDouble(dtb.Rows[0]["amount"]) ?? 0;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            throw new Exception("ข้อมูลมีปัญหา");
        }

        public DataTable GetAddExpenseList(int bgm_proj_oper_seq, int money_req_seq)
        {
            DataTable dtb = null;
            string strSQL = "";
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);
                strSQL = @"
select ope.bgm_proj_oper_exp_seq as value, exp.bgm_exp_name as text

from bgm_project_operation_expense ope
left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
left join bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq
left join fim_money_request_dtls mrd on ope.bgm_proj_oper_exp_seq = mrd.bgm_proj_oper_exp_seq
    and mrd.money_req_seq = @money_req_seq

where ope.record_status <> 'D'
and ope.bgm_proj_oper_seq = @bgm_proj_oper_seq
and mrd.bgm_proj_oper_exp_seq is null
";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            throw new Exception("ข้อมูลมีปัญหา");
        }
    }

    public class M_BGM_ProjectOperation
    {
        public M_BGM_ProjectOperation()
        {

        }

        public int bgm_proj_oper_seq
        {
            get;
            set;
        }

        public string bgm_proj_oper_code
        {
            get;
            set;
        }

        public string bgm_proj_oper_name
        {
            get;
            set;
        }

        public string bgm_proj_oper_descr
        {
            get;
            set;
        }

        public Nullable<DateTime> bgm_proj_oper_date
        {
            get;
            set;
        }

        public Nullable<DateTime> start_date
        {
            get;
            set;
        }

        public Nullable<DateTime> end_date
        {
            get;
            set;
        }

        public Nullable<int> attend_count
        {
            get;
            set;
        }

        public Nullable<int> bgm_year
        {
            get;
            set;
        }

        public Nullable<int> bgm_proj_seq
        {
            get;
            set;
        }

        public Nullable<int> emp_seq
        {
            get;
            set;
        }

        public Nullable<int> job_site_seq
        {
            get;
            set;
        }

        public string is_use
        {
            get;
            set;
        }

        public Nullable<double> sum_amount
        {
            get;
            set;
        }

        //public M_BGM_Project m_bgm_project
        //{
        //    get;
        //    set;
        //}

        //public M_HRM_PS_Employees m_hrm_ps_employees
        //{
        //    get;
        //    set;
        //}

        //public M_HRM_MP_Jobsites m_hrm_mp_jobsites
        //{
        //    get;
        //    set;
        //}

        //public M_BGM_MAS_Option m_bgm_mas_option
        //{
        //    get;
        //    set;
        //}

        public string record_status
        {
            get;
            set;
        }

        public string record_status_name
        {
            get;
            set;
        }

        public Nullable<int> created_by
        {
            get;
            set;
        }

        public Nullable<DateTime> created_when
        {
            get;
            set;
        }

        public Nullable<int> updated_by
        {
            get;
            set;
        }

        public Nullable<DateTime> updated_when
        {
            get;
            set;
        }
    }
}
